"""
解析从数仓导出的M9 M10 M17数据文件并存到dolphindb
"""
import pandas as pd
from xqdata_ddb import add_attribute,upsert_attribute
from gypb.db import get_db

M17_ATTRS = {
    "holding_volume": "持仓量",
    "available_volume": "可用量",
    "trade_freezed_volume": "交易冻结量",
    "exception_freezed_volume": "异常冻结量",
    "fit_volume": "在途量",
    "market_value": "持仓市值",
}

M17_EXCEL_ATTR_MAPPER = {
    "holding_volume": "股份余额",
    "available_volume": "可用数量",
    "trade_freezed_volume": "交易冻结数量",
    "exception_freezed_volume": "异常冻结数量",
    "fit_volume": "在途数量",
    "market_value": "持仓成本",
}
ATTR_M17_EXCEL_MAPPER = {v:k for k,v in M17_EXCEL_ATTR_MAPPER.items()}

M17_CSV_ATTR_MAPPER = {
    "holding_volume": "hold_vlm",
    "available_volume": "avlb_vlm",
    "trade_freezed_volume": "trd_frzn_vlm",
    "exception_freezed_volume": "excp_frzn_vlm",
    "fit_volume": "flt_vlm",
    "market_value": "pos_mval",
}
ATTR_M17_CSV_MAPPER = {v:k for k,v in M17_CSV_ATTR_MAPPER.items()}

def add_m17_attribute():
    with get_db() as session:
        for attr,attr_name in M17_ATTRS.items():
            add_attribute(session, "position", attr, attr_name, code_dtype="SYMBOL",obj_dtype="LONG")

def m17_csv_parser(m17_csv_path)->pd.DataFrame:
    m17_df = pd.read_csv(m17_csv_path) # csv文件经常有问题，建议用excel
    m17_df["datetime"] = pd.to_datetime(m17_df["busi_date"])
    m17_df["exchange"] = m17_df["mrkt_code"].map({"0000-上海证券交易所A股":"SSE","0100-深圳证券交易所A股":"SZSE"})
    m17_df["scode"] = m17_df["scode"].apply(lambda x: '{:0>6}'.format(x))
    m17_df["code"] = m17_df["scode"] + "." + m17_df["exchange"]
    m17_df["object"] = m17_df["caccn_num1"]
    m17_df = m17_df.set_index(["datetime","code","object"])[[M17_CSV_ATTR_MAPPER.values()]]
    m17_df.rename(columns=ATTR_M17_CSV_MAPPER, inplace=True)
    return m17_df

    # upsert_attribute(session, "position", m17_df)


def m17_excel_parser(m17_df:pd.DataFrame)->pd.DataFrame:
    m17_df["datetime"] = pd.to_datetime(m17_df["业务日期"],format="%Y%m%d")
    def get_exchange_with_dot_prefix(x):
        if "港股" in x:
            return ".HK"
        elif "上海" in x:
            return ".SSE"
        elif "深圳" in x:
            return ".SZSE"
        elif "北京" in x:
            return ".BJSE"        
        elif "股份转让" in x:
            return ".NEEQ"
        elif "场外开放式基金" in x:
            return ".OF"
        elif "场外机构间市场" in x:
            return ""
        
    m17_df["exchange"] = m17_df["市场代码"].map(get_exchange_with_dot_prefix)
    # m17_df["证券代码"] = m17_df["证券代码"].apply(lambda x: '{:0>6}'.format(x))
    m17_df["code"] = m17_df["证券代码"] + m17_df["exchange"]
    m17_df["object"] = m17_df["资金账户"]
    m17_df = m17_df.set_index(["datetime","code","object"])[ATTR_M17_EXCEL_MAPPER.keys()]
    m17_df.rename(columns=ATTR_M17_EXCEL_MAPPER, inplace=True)
    m17_df = m17_df.reset_index(["datetime","code","object"]).groupby(["datetime","code","object"],group_keys=True,as_index=True).sum()
    return m17_df
    # upsert_attribute(session, "position", m17_df)

if __name__ == "__main__":
    # pass
    # add_m17_attribute()
    m17_file = "src\gypb/apps\performance_analysis\M_17_产品户持仓日明细汇总20250317.xlsx"
    raw_m17_df = pd.read_excel(m17_file,header=1)
    data = m17_excel_parser(raw_m17_df)
    print(data.head())
    # with get_db() as session:
    #     upsert_attribute(session, "position", data)
    
    